﻿using aier.dataaccess.model;
using Dapper;
using log4net;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace aier.dataaccess.dapper
{
    public class DoctorDal
    {
        public DapperHelper dapperHelper { get; set; }
        public ILog log { get; set; }



        /// <summary>
        /// 学术推广首页点击查看详情进入
        /// </summary>
        /// <param name="liveness"></param>
        /// <param name="ownerid"></param>
        /// <param name="new_districtandcounty"></param>
        /// <param name="isml">是否医联体</param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="type">0:开发量；1：建档量</param>
        /// <returns></returns>
        public int QryDoctorListCountByConditions(string liveness, Guid ownerid, Guid new_districtandcounty, bool isml, DateTime? startTime, DateTime? endTime, int? type,string doctorname)
        {
            return dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select count(1) from dbo.AIER_CONTACT_INFO /**where**/";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where("statecode = 0");
                if (!string.IsNullOrWhiteSpace(liveness))
                {
                    if (liveness != "null")
                    {
                        sqlBuilder.Where("new_liveness = @liveness", new { liveness = liveness });
                    }
                }
                if (!string.IsNullOrWhiteSpace(doctorname))
                {
                    sqlBuilder.Where($@"lastName like '%{doctorname}%'");
                }
                if (ownerid != Guid.Empty)
                {
                    sqlBuilder.Where("ownerid = @ownerid", new { ownerid = ownerid });
                }
                if (new_districtandcounty != Guid.Empty)
                {
                    sqlBuilder.Where("new_districtandcounty = @new_districtandcounty", new { new_districtandcounty = new_districtandcounty });
                }
                if (isml)
                {
                    sqlBuilder.Where("new_iscoopmedicalcouplet=1");
                }

                else
                {
                    sqlBuilder.Where(" (new_type = 1 or new_type = 3 or new_type = 4)");
                }

                if (type == 1)
                {
                    sqlBuilder.Where("new_cooperativeintention=1");
                }
                if (startTime!=null&&endTime!=null){
                    sqlBuilder.Where(" convert(varchar(10),createdon,23)<=@endTime and convert(varchar(10),createdon,23)>=@startTime", new { startTime = startTime, endTime = endTime });
                }
                return a.ExecuteScalar<int>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        /// <summary>
        ///根据条件获取医生列表
        /// </summary>
        /// </param>
        /// <returns></returns>
        public IEnumerable<ContactView> QryDoctorList(string liveness, Guid ownerid, Guid new_districtandcounty,bool isml)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ContactView>>(a =>
            {
                string sql = @"select top 500 contactid,LastName,new_typename,new_hospitalnameName,new_hospitalname
                                    ,new_liveness,new_districtandcounty
                                    ,new_marketName+'-'+new_districtandcountyName as new_districtandcountyName    
                                    ,New_title
                                from dbo.AIER_CONTACT_INFO /**where**/ /**order by**/";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where("statecode = 0 and (new_type = 1 or new_type = 3 or new_type = 4)");
                sqlBuilder.OrderBy("createdon desc");
                if (!string.IsNullOrWhiteSpace(liveness))
                {
                    if (liveness != "null")
                    {
                        sqlBuilder.Where("new_liveness = @liveness", new { liveness = liveness });
                    }
                    else {
                        sqlBuilder.Where("new_cooperativeintention = 0");
                    }
                   
                }
                if (ownerid != Guid.Empty)
                {
                    sqlBuilder.Where("ownerid = @ownerid", new { ownerid = ownerid });
                }
                if (new_districtandcounty != Guid.Empty)
                {
                    sqlBuilder.Where("new_districtandcounty = @new_districtandcounty", new { new_districtandcounty = new_districtandcounty });
                }
                if (isml)
                {
                    sqlBuilder.Where("new_iscoopmedicalcouplet=1");
                }
                return a.Query<ContactView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        /// <summary>
        /// 学术推广首页点击查看详情进入
        /// </summary>
        /// <param name="liveness"></param>
        /// <param name="ownerid"></param>
        /// <param name="new_districtandcounty"></param>
        /// <param name="isml"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="type">0:开发量；1：建档量</param>
        /// <returns></returns>
        public IEnumerable<ContactView> QryDoctorList(string liveness, Guid ownerid, Guid new_districtandcounty, bool isml,DateTime? startTime,DateTime? endTime,int type,string doctorname)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ContactView>>(a =>
            {
                string sql = @"select top 500 contactid,LastName,new_typename,new_hospitalnameName,new_hospitalname
                                    ,new_liveness,new_districtandcounty
                                    ,new_marketName+'-'+new_districtandcountyName as new_districtandcountyName                       
                                from dbo.AIER_CONTACT_INFO /**where**/";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where("statecode = 0");
                if (!string.IsNullOrWhiteSpace(liveness))
                {
                    if (liveness != "null")
                    {
                        sqlBuilder.Where("new_liveness = @liveness", new { liveness = liveness });
                    }
                }
                if (!string.IsNullOrWhiteSpace(doctorname)) {
                    sqlBuilder.Where($@"lastName like '%{doctorname}%'");
                }
                if (ownerid != Guid.Empty)
                {
                    sqlBuilder.Where("ownerid = @ownerid", new { ownerid = ownerid });
                }
                if (new_districtandcounty != Guid.Empty)
                {
                    sqlBuilder.Where("new_districtandcounty = @new_districtandcounty", new { new_districtandcounty = new_districtandcounty });
                }

                //是否目邻 改为医联体  2020/2/11 Louis
                if (isml) {
                    sqlBuilder.Where("new_iscoopmedicalcouplet=1");
                }
                sqlBuilder.Where(" (new_type = 1 or new_type = 3 or new_type = 4)");
                //if (isml)
                //{
                //    sqlBuilder.Where("new_type=@new_type", new { new_type = 4 });
                //}
                //else {
                //    sqlBuilder.Where(" (new_type = 1 or new_type = 2 or new_type = 4)");
                //}

                if (type == 1)
                    sqlBuilder.Where("new_cooperativeintention=1");
               // if (startTime.ToString() != "1900/1/1 0:00:00" && endTime.ToString() != "1900/1/1 0:00:00")
                //{
                    sqlBuilder.Where(" convert(varchar(10),createdon,23)<=@endTime and convert(varchar(10),createdon,23)>=@startTime", new { startTime = startTime, endTime = endTime });
                //}

                sqlBuilder.OrderBy("createdon desc");
                return a.Query<ContactView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        /// <summary>
        ///获取区域
        /// </summary>
        /// </param>
        /// <returns></returns>
        public IEnumerable<ContactView> QryAreaByConditions(Guid ownerid)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ContactView>>(a =>
            {
                string sql = @"select distinct new_districtandcounty as new_districtandcounty
                                    ,new_marketName+'-'+new_districtandcountyName as new_districtandcountyName
                                from dbo.AIER_CONTACT_INFO /**where**/";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where("statecode = 0 and (new_type = 1 or new_type = 3 or new_type = 4)");


                if (ownerid != Guid.Empty)
                {
                    sqlBuilder.Where("ownerid = @ownerid", new { ownerid = ownerid });
                }

                sqlBuilder.Where("new_districtandcounty is not null");
                return a.Query<ContactView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }


        /// <summary>
        /// 根据id获取医生
        /// </summary>
        /// </param>
        /// <returns></returns>
        public ContactView QryDoctorById(Guid doctorid)
        {
            return dapperHelper.ExecueWithoutTransaction<ContactView>(a =>
            {
                string sql = @"select * from dbo.AIER_CONTACT_INFO /**where**/";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where("contactid = @doctorid ", new { doctorid = doctorid });
                return a.Query<ContactView>(sqlTemplate.RawSql, sqlTemplate.Parameters).First();
            });
        }



    }
}
